﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
/* OpenXml */
using System.IO;
using System.Xml;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using Novacode;
/* OpenXml */


namespace KPIS.GERP.GFMS.APP.App_UI.Commons
{
    public class MailMerge
    {
        Main main = new Main();

        public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
            , Dictionary<string, string> fields
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , List<Dictionary<string, string>> Dics = null
            , Dictionary<string, bool> areas = null)
        {
            bool bOK = false;

            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                try
                {
                    if (fields != null)
                    {
                        foreach (var field in fields)
                        {
                            document.ReplaceText("«" + field.Key + "»", field.Value);
                        }
                    }

                    if (dtbs != null)
                    {
                        foreach (var _dtb in dtbs)
                        {
                            System.Data.DataTable dtb = _dtb.Value;
                            for (int i = 0; i < dtb.Rows.Count; i++)
                            {
                                System.Data.DataRow dr = dtb.Rows[i];

                                foreach (System.Data.DataColumn col in dtb.Columns)
                                {
                                    var ColumnName = col.ColumnName + (i + 1).ToString();
                                    string strValue = dr[ColumnName].ToString();

                                    document.ReplaceText("«" + ColumnName + "»", strValue);
                                }
                            }
                        }
                    }

                    if (Dics != null && Dics.Count > 0)
                    {
                        for (int i = 0; i < Dics.Count; i++)
                        {
                            var Dic = Dics[i];

                            foreach (var DicItem in Dic)
                            {
                                string ColumnName = DicItem.Key + (i + 1).ToString();
                                string strValue = DicItem.Value;

                                document.ReplaceText("«" + ColumnName + "»", strValue);
                            }
                        }
                    }

                    if (areas != null)
                    {
                    }

                    while (document.Text.IndexOf("«") >= 0)
                    {
                        int Left = document.Text.IndexOf("«");
                        int Right = document.Text.IndexOf("»");

                        string strText = document.Text.Substring(Left, Right - Left + 1);
                        document.ReplaceText(strText, "");
                    }

                    document.SaveAs(strSaveAsFilePath);
                    bOK = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                }
            }

            return bOK;
        }

        public void SaveFileToClient(HttpResponse response, string strFilePath, string strFileName, bool bDeleteFile)
        {
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
            response.WriteFile(strFilePath + strFileName);
            response.Flush();
            if (bDeleteFile)
            {
                System.IO.File.Delete(strFilePath + strFileName);
            }
            response.End();
        }

        public bool MergeExcel(HttpResponse response, string strTemplateFilePath, string strFileName
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , Dictionary<string, string> Dics = null
            , List<string> mergeFieldList = null
            , string TemplateWorkSheetName = null
            , OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.None
            
            )
        {
            bool bOK = false;

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    if (dtbs != null)
                    {
                        foreach (var dtb in dtbs)
                        {
                            if (dtb.Value.Rows.Count > 0)
                            {
                                ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false);                                
                            }
                        }
                    }

                    if (Dics != null)
                    {
                        foreach (var Dic in Dics)
                        {
                            if (Dic.Key.IndexOf('!') > -1)
                            {
                                string strSheetName = Dic.Key.Split('!')[0];
                                string strCell = Dic.Key.Split('!')[1];

                                var ws2 = pck.Workbook.Worksheets[strSheetName];

                                if (ws2 == null)
                                {
                                    if (TemplateWorkSheetName != null)
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName, pck.Workbook.Worksheets[TemplateWorkSheetName]);
                                    }
                                    else
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName);
                                    }
                                    ws2 = pck.Workbook.Worksheets[strSheetName];
                                }

                                if (Dic.Value.ToString().StartsWith("@n"))
                                {
                                    ws2.Cells[Dic.Key].Value = main.ConvertNullDouble(Dic.Value.Replace("@n", "")) ?? 0;
                                }
                                else
                                {
                                    ws2.Cells[Dic.Key].Value = Dic.Value;
                                }
                            }

                            if (Dic.Value.ToString().StartsWith("@n"))
                            {
                                ws.Cells[Dic.Key].Value = main.ConvertNullDouble(Dic.Value.Replace("@n", "")) ?? 0;
                            }
                            else
                            {
                                ws.Cells[Dic.Key].Value = Dic.Value;
                            }
                        }
                    }

                    if (mergeFieldList != null)
                    {
                        foreach (var mf in mergeFieldList)
                        {
                            if (mf.IndexOf('!') >= 0)
                            {
                                string strSheetName = mf.Split('!')[0];
                                string strCell = mf.Split('!')[1];

                                var ws2 = pck.Workbook.Worksheets[strSheetName];

                                if (ws2 == null)
                                {
                                    if (TemplateWorkSheetName != null)
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName, pck.Workbook.Worksheets[TemplateWorkSheetName]);
                                    }
                                    else
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName);
                                    }
                                    ws2 = pck.Workbook.Worksheets[strSheetName];
                                }

                                ws2.Cells[mf].Merge = true;
                            }
                            else
                            {
                                ws.Cells[mf].Merge = true;
                            }
                        }
                    }
                    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));
                    response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
            return bOK;
        }

        public bool MergeExcel(HttpResponse response, string strTemplateFilePath, string strFileName
           , int[] colPosition
           , Dictionary<string, System.Data.DataTable> dtbs = null
           , Dictionary<string, string> Dics = null
           , List<string> mergeFieldList = null
           , string TemplateWorkSheetName = null
           , OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.None           
           )
        {
            bool bOK = false;

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    if (dtbs != null)
                    {
                        foreach (var dtb in dtbs)
                        {
                            if (dtb.Value.Rows.Count > 0)
                            {
                                ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false);
                            }
                        }
                    }

                    if (Dics != null)
                    {
                        foreach (var Dic in Dics)
                        {
                            if (Dic.Key.IndexOf('!') > -1)
                            {
                                string strSheetName = Dic.Key.Split('!')[0];
                                string strCell = Dic.Key.Split('!')[1];

                                var ws2 = pck.Workbook.Worksheets[strSheetName];

                                if (ws2 == null)
                                {
                                    if (TemplateWorkSheetName != null)
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName, pck.Workbook.Worksheets[TemplateWorkSheetName]);
                                    }
                                    else
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName);
                                    }
                                    ws2 = pck.Workbook.Worksheets[strSheetName];
                                }

                                if (Dic.Value.ToString().StartsWith("@n"))
                                {
                                    ws2.Cells[Dic.Key].Value = main.ConvertNullDouble(Dic.Value.Replace("@n", "")) ?? 0;
                                }
                                else
                                {
                                    ws2.Cells[Dic.Key].Value = Dic.Value;
                                }
                            }

                            if (Dic.Value.ToString().StartsWith("@n"))
                            {
                                ws.Cells[Dic.Key].Value = main.ConvertNullDouble(Dic.Value.Replace("@n", "")) ?? 0;
                            }
                            else
                            {
                                ws.Cells[Dic.Key].Value = Dic.Value;
                            }
                        }
                    }

                    if (mergeFieldList != null)
                    {
                        foreach (var mf in mergeFieldList)
                        {
                            if (mf.IndexOf('!') >= 0)
                            {
                                string strSheetName = mf.Split('!')[0];
                                string strCell = mf.Split('!')[1];

                                var ws2 = pck.Workbook.Worksheets[strSheetName];

                                if (ws2 == null)
                                {
                                    if (TemplateWorkSheetName != null)
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName, pck.Workbook.Worksheets[TemplateWorkSheetName]);
                                    }
                                    else
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName);
                                    }
                                    ws2 = pck.Workbook.Worksheets[strSheetName];
                                }

                                ws2.Cells[mf].Merge = true;
                            }
                            else
                            {
                                ws.Cells[mf].Merge = true;
                            }
                        }
                    }

                    var border = ws.Cells[colPosition[0], colPosition[1], colPosition[2], colPosition[3]].Style.Border;
                    border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;

                    var border2 = ws.Cells[colPosition[2], colPosition[1], colPosition[2], colPosition[3]].Style.Border;
                    border2.Bottom.Style = border2.Top.Style = ExcelBorderStyle.Thin;

                    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));
                    response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
            return bOK;
        }


        #region[Export Excel]

        public void WriteDicToExcel(HttpResponse Response, Dictionary<string, string> _dtM, string type, string path)
        {            
            if (_dtM.Count <= 0) return;
            CheckFolderExist(path);

            FileInfo newFile = new FileInfo(Path.Combine(path, type + "Template.xlsx"));
            if (!newFile.Exists) return;

            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                //////you can customize this method to do your excel
                if (_dtM.Count > 0)
                    AddWorkSheet(_dtM, "M", ':', xlPackage);

                //////write file excel
                WriteFile(Response, type, xlPackage);
            }
        }

        public void WriteDicToExcel(HttpResponse Response, List<Dictionary<string, object>> dicList, string type, string path,string filename, string[] arrSheetName)
        {
            if (dicList.Count <= 0) return;
            CheckFolderExist(path);

            FileInfo newFile = new FileInfo(path + filename);
            if (!newFile.Exists) return;

            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                //////you can customize this method to do your excel
                if (dicList.Count > 0)
                {
                    int i = 0;

                    foreach (Dictionary<string, object> item in dicList)
                    {
                        AddWorkSheet(item,(arrSheetName[i]).ToString(),':', xlPackage);                       
                        i++;                        
                    }                    
                }
                //////write file excel
                WriteFile(Response, type, xlPackage);
            }
        }


        private static void AddWorkSheet(Dictionary<string, object> _dtM, string _sheet, char _char, ExcelPackage xlPackage)
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Copy("Template",_sheet);
            if (worksheet != null)
            {
                foreach (KeyValuePair<string, object> item in _dtM)
                {
                    string[] _cell = item.Key.Split(_char);
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);

                        worksheet.Cells[row, col].Value = item.Value;                       
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new System.Drawing.Font("TH SarabunPSK", 14, FontStyle.Regular));
                        if (col == 15 || col == 18)
                        {                            
                            worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                            worksheet.Cells[row, col].Style.Numberformat.Format = "#,##0.00";
                        }
                        else
                        {
                            worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                        } 
                    }
                }
            }
        }

        private static void AddWorkSheet(Dictionary<string, string> _dtM, string _sheet, char _char, ExcelPackage xlPackage)
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Copy("Template", _sheet);
            if (worksheet != null)
            {
                foreach (KeyValuePair<string, string> item in _dtM)
                {
                    string[] _cell = item.Key.Split(_char);
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);

                        worksheet.Cells[row, col].Value = item.Value;
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new System.Drawing.Font("TH SarabunPSK", 14, FontStyle.Regular));
                        worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                }
            }
        }

        private void WriteFile(HttpResponse Response,string type, ExcelPackage xlPackage)
        {
            //you can customize file name
            string filename = type + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(xlPackage.GetAsByteArray());
            Response.Flush();
            Response.End();
        }

        private void CheckFolderExist(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
                Main main = new Main();
                main.FolderACL("Everyone", path);
            }
        }
        #endregion[Export Excel]

        //public string ThaiBaht(string txt)
        //{
        //    string bahtTxt, n, bahtTH = "";
        //    double amount;
        //    try { amount = Convert.ToDouble(txt); }
        //    catch { amount = 0; }
        //    bahtTxt = amount.ToString("####.00");
        //    string[] num = { "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า", "สิบ" };
        //    string[] rank = { "", "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน", "สิบ" };
        //    string[] temp = bahtTxt.Split('.');
        //    string intVal = temp[0];
        //    string decVal = temp[1];
        //    if (Convert.ToDouble(bahtTxt) == 0)
        //        bahtTH = "ศูนย์บาทถ้วน";
        //    else
        //    {
        //        for (int i = 0; i < intVal.Length; i++)
        //        {
        //            n = intVal.Substring(i, 1);
        //            if (n != "0")
        //            {
        //                if ((i == (intVal.Length - 1)) && (n == "1"))
        //                    bahtTH += "เอ็ด";
        //                else if ((i == (intVal.Length - 2)) && (n == "2"))
        //                    bahtTH += "ยี่";
        //                else if ((i == 8) && (n == "1"))
        //                    bahtTH += "";
        //                else if ((i == 8) && (n == "2"))
        //                    bahtTH += "ยี่";
        //                else
        //                    bahtTH += num[Convert.ToInt32(n)];
        //                bahtTH += rank[(intVal.Length - i) - 1];
        //            }
        //        }
        //        bahtTH += "บาท";
        //        if (decVal == "00")
        //            bahtTH += "ถ้วน";
        //        else
        //        {
        //            for (int i = 0; i < decVal.Length; i++)
        //            {
        //                n = decVal.Substring(i, 1);
        //                if (n != "0")
        //                {
        //                    if ((i == decVal.Length - 1) && (n == "1"))
        //                        bahtTH += "เอ็ด";
        //                    else if ((i == (decVal.Length - 2)) && (n == "2"))
        //                        bahtTH += "ยี่";
        //                    else if ((i == (decVal.Length - 2)) && (n == "1"))
        //                        bahtTH += "";
        //                    else
        //                        bahtTH += num[Convert.ToInt32(n)];
        //                    bahtTH += rank[(decVal.Length - i) - 1];
        //                }
        //            }
        //            bahtTH += "สตางค์";
        //        }
        //    }
        //    return bahtTH;
        //}
    }

    public class BahtText
    {
        private static string s1 = "";
        private static string s2 = "";
        private static string s3 = "";
        private static string[] suffix = { "", "", "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน" };
        private static string[] numSpeak = { "", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า" };

        public static string ToBahtText(double m)
        {
            string result;

            if (m == 0) return ("");

            splitCurr(m);
            result = "";
            if (s1.Length > 0)
            {
                result = result + Speak(s1) + "ล้าน";
            }
            if (s2.Length > 0)
            {
                result = result + Speak(s2) + "บาท";
            }
            if (s3.Length > 0)
            {
                result = result + speakStang(s3) + "สตางค์";
            }
            else
            {
                result = result + "ถ้วน";
            }
            return (result);
        }

        private static string Speak(string s)
        {
            int L, c;
            string result;

            if (s == "") return ("");

            result = "";
            L = s.Length;
            for (int i = 0; i < L; i++)
            {
                if ((s.Substring(i, 1) == "-"))
                {
                    result = result + "ติดลบ";
                }
                else
                {
                    c = System.Convert.ToInt32(s.Substring(i, 1));
                    if ((i == L - 1) && (c == 1))
                    {
                        if (L == 1)
                        {
                            return ("หนึ่ง");
                        }
                        if ((L > 1) && (s.Substring(L - 1, 1) == "0"))
                        {
                            result = result + "หนึ่ง";
                        }
                        else
                        {
                            result = result + "เอ็ด";
                        }
                    }
                    else if ((i == L - 2) && (c == 2))
                    {
                        result = result + "ยี่สิบ";
                    }
                    else if ((i == L - 2) && (c == 1))
                    {
                        result = result + "สิบ";
                    }
                    else
                    {
                        if (c != 0)
                        {
                            result = result + numSpeak[c] + suffix[L - i];
                        }
                    }
                }
            }
            return (result);
        }

        private static string speakStang(string s)
        {
            int L, c;
            string result;

            L = s.Length;

            if (L == 0) return ("");

            if (L == 1)
            {
                s = s + "0";
                L = 2;
            }
            if (L > 2)
            {
                s = s.Substring(0, 2);
                L = 2;
            }
            result = "";
            for (int i = 0; i < 2; i++)
            {
                c = Convert.ToInt32(s.Substring(i, 1));
                if ((i == L - 1) && (c == 1))
                {
                    if (Convert.ToInt32(s.Substring(0, 1)) == 0)
                        result = result + "หนึ่ง";
                    else
                        result = result + "เอ็ด";
                }
                else if ((i == L - 2) && (c == 2))
                {
                    result = result + "ยี่สิบ";
                }
                else if ((i == L - 2) && (c == 1))
                {
                    result = result + "สิบ";
                }
                else
                {
                    if (c != 0)
                    {
                        result = result + numSpeak[c] + suffix[L - i];
                    }
                }
            }

            return (result);
        }

        private static void splitCurr(double m)
        {
            string s;
            int L;
            int position;

            s = System.Convert.ToString(m);
            position = s.IndexOf(".");
            if ((position >= 0))
            {
                s1 = s.Substring(0, position);
                s3 = s.Substring(position + 1);
                if (s3 == "00")
                {
                    s3 = "";
                }
            }
            else
            {
                s1 = s;
                s3 = "";
            }
            L = s1.Length;
            if ((L > 6))
            {
                s2 = s1.Substring(L - 6);
                s1 = s1.Substring(0, L - 6);
            }
            else
            {
                s2 = s1;
                s1 = "";
            }

            if ((s1 != "") && (Convert.ToInt32(s1) == 0)) s1 = "";
            if ((s2 != "") && (Convert.ToInt32(s2) == 0)) s2 = "";
        }

    }
}